﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ShopB2C.DBUtilities;
using ShopB2C.DBUtilities.SqlDbHelper;
using ShopB2C.Entity;
using ShopB2C.Enums;
using ShopB2C.Interfaces;
using ShopB2C.ViewModels;

namespace ShopB2C.SQLDataAccess {
    public class ProductPreView : IProductPreview {

        #region 存储过程

        private const string _search = "Product_Search";
        private const string _batch = "Product_Batch";
        private const string _batchOneByOne = "Product_Batch_OneByOne";
        private const string _selectByIds = "Product_SelectByIds";
        private const string _partList = "Product_Part_PartList";
        private const string _partSelect = "Product_Part_Select";
        private const string _sameSelect = "Product_Part_Same";


        /// <summary>
        /// 商品预览
        /// </summary>
        private const string _productView = "Product_View";
        /// <summary>
        /// 属性列表
        /// </summary>
        private const string _propertyList = "Product_Property_SelectList";
        /// <summary>
        /// 图片列表
        /// </summary>
        private const string _imageList = "Product_Image_List";
        /// <summary>
        /// 获取详细信息
        /// </summary>
        private const string _getDetails = "Product_Details_Select";
        /// <summary>
        /// 会员折扣
        /// </summary>
        private const string _discount = "Product_Discount_List";
        /// <summary>
        /// 获取扩展属性列表
        /// </summary>
        private const string _extendList = "Product_Extend_List";
        /// <summary>
        /// 根据产品特性值过滤
        /// </summary>
        private const string _filter = "Product_Filter";
        /// <summary>
        ///  相关产品
        /// </summary>
        private const string _relateSelect = "Product_Relate_Select";

        #endregion

        public ProductPreView(string connectionString) {
            DbBase.ConnectionString = connectionString;
        }

        public List<ProductEntity> Search(ProductSearchCondition psc, out int pageCount) {
            int rowsCount;
            return this.Search(psc, out pageCount, out rowsCount);
        }

        public List<ProductEntity> Search(ProductSearchCondition psc, out int pageCount, out int rowsCount) {
            pageCount = 0;
            rowsCount = 0;
            List<ProductEntity> listResult = new List<ProductEntity>();

            DbParams<SqlParameter> parameters = new DbParams<SqlParameter>();
            parameters.Add<int>("@ClassifyId", psc.ClassifyId).SqlDbType = SqlDbType.Int;
            parameters.Add<string>("@PropertyValIds", 4000, psc.PropertyValIds).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<int>("@Status", (int)psc.Status).SqlDbType = SqlDbType.Int;
            parameters.Add<string>("@Key", 20, psc.Key).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<int>("@SortIndex", (int)psc.SortIndex).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@SortDescription", (int)psc.SortDescription).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@Uid", psc.UserId).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@PageSize", psc.PageSize).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@PageIndex", psc.PageIndex).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@ProductStatus", (int)psc.ProductStatus).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@BrandId", psc.BrandId).SqlDbType = SqlDbType.Int;
            parameters.Add<bool?>("@IsActive", psc.IsActive).SqlDbType = SqlDbType.Bit;
            parameters.Add<decimal>("@PriceFrom", psc.PriceFrom).SqlDbType = SqlDbType.Decimal;
            parameters.Add<decimal>("@PriceTo", psc.PriceTo).SqlDbType = SqlDbType.Decimal;
            parameters.Add<string>("@ModuleKey", psc.ModuleKey).SqlDbType = SqlDbType.NVarChar;

            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_search, parameters.ToArray);
            if (dr.HasRows) {
                while (dr.Read()) {
                    pageCount = (int)dr["PageCount"];
                    rowsCount = (int)dr["RowsCount"];
                    listResult.Add(new ProductEntity() {
                        ProductId = (int)dr["ProductId"],
                        ProductName = dr["ProductName"].ToString(),
                        SubName = dr["SubName"].ToString(),
                        Description = dr["Description"].ToString(),
                        KeyWords = dr["KeyWords"].ToString(),
                        Price = (decimal)dr["Price"],
                        ClickCount = (int)dr["ClickCount"],
                        ReplyCount = (int)dr["ReplyCount"],
                        GradeValue = (decimal)dr["GradeValue"],
                        StockCount = (int)dr["StockCount"],
                        SalesCount = (int)dr["SalesCount"],
                        AddDateTime = (DateTime)dr["AddDateTime"],
                        IsActive = (bool)dr["IsActive"],
                        SaleDateTime = (DateTime)dr["SaleDateTime"],
                        DisCount = (double)dr["DisCount"],
                        ProductNo = dr["ProductNo"].ToString(),
                        ClassId = (int)dr["ClassId"],
                        ImageName = dr["ImageName"].ToString(),
                        BrandId = (int)dr["BrandId"],
                        Status = (ProductStatus)dr["Status"],
                        MyDiscount = (double)dr["MyDiscount"],
                        BrandName = dr["BrandName"].ToString(),
                        ClassifyName = dr["ClassifyName"].ToString()
                    });
                }
            }
            dr.Close();
            dr.Dispose();

            return listResult;
        }

        public int Batch(string productIds, string actions, int stock, float discount,
            DateTime sales, ProductStatus status, int brandId) {

            DbParams<SqlParameter> parameters = new DbParams<SqlParameter>();
            parameters.Add<string>("@ProductIds", 4000, productIds).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@Actions", 10, actions).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<DateTime>("@SaleDateTime", sales).SqlDbType = SqlDbType.DateTime;
            parameters.Add<int>("@Stock", stock).SqlDbType = SqlDbType.Int;
            parameters.Add<float>("@Discount", discount).SqlDbType = SqlDbType.Float;
            parameters.Add<int>("@Status", (int)status).SqlDbType = SqlDbType.Int;
            parameters.Add<int>("@BrandId", brandId).SqlDbType = SqlDbType.Int;

            return SqlDbProcedure.ExecuteNonQuery(_batch, parameters.ToArray);
        }

        public int Batch(string productIds, string stocks, string discounts, string sales,
            string status, string brands, string actives) {

            DbParams<SqlParameter> parameters = new DbParams<SqlParameter>();
            parameters.Add<string>("@ProductIds", 4000, productIds).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@Stocks", 4000, stocks).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@Discounts", 4000, discounts).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@SaleDateTimes", 4000, sales).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@Status", 4000, status).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@BrandIds", 4000, brands).SqlDbType = SqlDbType.NVarChar;
            parameters.Add<string>("@Actives", 4000, actives).SqlDbType = SqlDbType.NVarChar;

            return SqlDbProcedure.ExecuteNonQuery(_batchOneByOne, parameters.ToArray);
        }

        public List<ProductEntity> GetProductByIds(string productIds) {
            List<ProductEntity> listResult = new List<ProductEntity>();

            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_selectByIds,
                DbParams<SqlParameter>.CreateParameter<string>("@ProductIds", 4000, productIds));
            if (dr.HasRows) {
                while (dr.Read()) {
                    listResult.Add(new ProductEntity() {
                        ProductId = (int)dr["ProductId"],
                        ProductName = dr["ProductName"].ToString(),
                        SubName = dr["SubName"].ToString(),
                        Description = dr["Description"].ToString(),
                        KeyWords = dr["KeyWords"].ToString(),
                        Price = (decimal)dr["Price"],
                        ClickCount = (int)dr["ClickCount"],
                        ReplyCount = (int)dr["ReplyCount"],
                        GradeValue = (decimal)dr["GradeValue"],
                        StockCount = (int)dr["StockCount"],
                        SalesCount = (int)dr["SalesCount"],
                        AddDateTime = (DateTime)dr["AddDateTime"],
                        IsActive = (bool)dr["IsActive"],
                        SaleDateTime = (DateTime)dr["SaleDateTime"],
                        DisCount = (double)dr["DisCount"],
                        ProductNo = dr["ProductNo"].ToString(),
                        ClassId = (int)dr["ClassId"],
                        BrandId = (int)dr["BrandId"],
                        Status = (ProductStatus)dr["Status"],
                        BrandName = dr["BrandName"].ToString(),
                        ImageName = dr["ImageName"].ToString()

                    });
                }
            }
            dr.Close();
            dr.Dispose();

            return listResult;
        }

        /// <summary>
        /// 商品预览
        /// </summary>
        /// <param name="productId">关联的商品ID</param>
        /// <returns>商品实体类</returns>
        public ProductEntity ProductView(int productId, int uid) {
            ProductEntity product = null;
            SqlParameter[] parameters = 
           {
               new SqlParameter("@ProductId",SqlDbType.Int)
           };
            parameters[0].Value = productId;
            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_productView, parameters);
            if (reader.HasRows) {
                product = new ProductEntity();
                reader.Read();

                product.ProductName = reader["ProductName"].ToString();
                product.SubName = reader["SubName"].ToString();
                product.ClassId = Convert.ToInt32(reader["ClassId"]);
                product.Description = reader["Description"].ToString();
                product.DisCount = Convert.ToDouble(reader["DisCount"]);
                product.IsActive = Convert.ToBoolean(reader["IsActive"]);
                product.KeyWords = reader["KeyWords"].ToString();
                product.Price = Convert.ToDecimal(reader["Price"]);
                product.ProductId = Convert.ToInt32(reader["ProductId"]);
                product.ProductNo = reader["ProductNo"].ToString();
                product.SaleDateTime = Convert.ToDateTime(reader["SaleDateTime"]);
                product.StockCount = Convert.ToInt32(reader["StockCount"]);
                product.ImageName = reader["ImageName"].ToString();
                product.ClickCount = Convert.ToInt32(reader["ClickCount"]);
                product.ReplyCount = Convert.ToInt32(reader["ReplyCount"]);
                product.ConsultCount = Convert.ToInt32(reader["ConsultCount"]);
                product.GradeValue = Convert.ToDecimal(reader["GradeValue"]);
                product.SalesCount = Convert.ToInt32(reader["SalesCount"]);
                product.BrandId = Convert.ToInt32(reader["BrandId"]);
                product.UnAuditCommentNum = Convert.ToInt32(reader["UnAuditCommentNum"]);
                product.UnAuditConsultNum = Convert.ToInt32(reader["UnAuditConsultNum"]);
            }
            reader.Close();
            return product;
        }
        /// <summary>
        /// 获取商品的属性列表
        /// </summary>
        /// <param name="productId">关联的商品ID</param>
        /// <returns>属性列表</returns>
        public List<ProductPropertyEntity> PropertyList(int productId, int? classId) {

            List<ProductPropertyEntity> propertyList = new List<ProductPropertyEntity>();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@ProductId",SqlDbType.Int){ Value = productId},
                new SqlParameter("@ClassId",SqlDbType.Int){Value = classId}
            };
            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_propertyList, parameters);
            if (reader.HasRows) {
                while (reader.Read()) {
                    propertyList.Add(new ProductPropertyEntity() {
                        ProductId = productId,
                        ProductPropertyId = Convert.ToInt32(reader["ProductPropertyId"]),
                        PropertyId = Convert.ToInt32(reader["PropertyId"]),
                        PropertyValId = Convert.ToInt32(reader["PropertyValId"]),
                        PropertyName = reader["Name"].ToString(),
                        PropertyValue = reader["PropertyValue"].ToString()
                    });
                }
            }
            reader.Close();
            return propertyList;

        }
        /// <summary>
        /// 获取商品图片扩展列表
        /// </summary>
        public List<ProductImageEntity> GetImageList(int productId) {
            List<ProductImageEntity> imageList = new List<ProductImageEntity>();

            SqlParameter[] parameters = 
            {
                new SqlParameter("@ProductId",SqlDbType.Int)
            };
            parameters[0].Value = productId;

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_imageList, parameters);
            if (reader.HasRows) {
                while (reader.Read()) {
                    imageList.Add(new ProductImageEntity() {
                        Description = reader["Description"].ToString(),
                        ImageId = Convert.ToInt32(reader["ImageId"]),
                        ImageName = reader["ImageName"].ToString(),
                        IsCover = Convert.ToBoolean(reader["IsCover"]),
                        ProductId = Convert.ToInt32(reader["ProductId"]),
                        Sequence = Convert.ToInt32(reader["Sequence"]),
                        Title = reader["Title"].ToString()
                    });
                }
            }
            reader.Close();

            return imageList;
        }
        /// <summary>
        /// 获取商品详细信息/售后服务/包装方式
        /// </summary>
        /// <param name="productId">关联的商品ID</param>
        public ProductDetailsEntity GetDetails(int productId) {
            ProductDetailsEntity details = new ProductDetailsEntity() { PackList = string.Empty, Detail = string.Empty, Services = string.Empty };
            SqlParameter[] parameters = 
            {
                new SqlParameter("@ProductId",SqlDbType.Int)
            };
            parameters[0].Value = productId;

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_getDetails, parameters);
            if (reader.HasRows) {
                reader.Read();
                details.Services = reader["Services"].ToString();
                details.Detail = reader["Detail"].ToString();
                details.PackList = reader["PackList"].ToString();
            }
            reader.Close();

            return details;
        }
        /// <summary>
        /// 获取产品的自定义扩展属性
        /// </summary>
        /// <param name="product">产品ID</param>
        /// <returns>属性列表</returns>
        public List<ProductExtendEntity> ProductExtendList(int productId) {
            List<ProductExtendEntity> extendList = new List<ProductExtendEntity>();
            SqlParameter[] parameters =
            {
                new SqlParameter("@ProductId",SqlDbType.Int)
                                        };
            parameters[0].Value = productId;

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_extendList, parameters);

            while (reader.Read()) {
                extendList.Add(new ProductExtendEntity() {
                    ExtendKey = reader["ExtendKey"].ToString(),
                    ExtendValue = reader["ExtendValue"].ToString(),
                    ExtendId = Convert.ToInt32(reader["ExtendId"]),
                    ProductId = Convert.ToInt32(reader["ProductId"]),
                    Sequence = Convert.ToInt32(reader["Sequence"])

                });
            }
            reader.Close();
            return extendList;
        }
        /// <summary>
        /// 会员组打折
        /// </summary>
        /// <param name="productId">关联的产品ID</param>
        /// <returns>折扣表</returns>
        public List<UserLevelEntity> MemberDiscount(int productId) {
            List<UserLevelEntity> list = new List<UserLevelEntity>();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@ProductId",SqlDbType.Int){Value = productId}
            };

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_discount, parameters);
            while (reader.Read()) {
                list.Add(new UserLevelEntity() {
                    Name = reader["Name"].ToString(),
                    LevelId = Convert.ToInt32(reader["LevelId"]),
                    IsDiscount = Convert.ToBoolean(reader["IsDiscount"]),
                    Discount = Convert.ToDouble(reader["Discount"])
                });
            }
            return list;
        }
        /// <summary>
        /// 产品详细页用来过滤产品图像的特性值
        /// </summary>
        /// <param name="classId">关联的产品ID</param>
        /// <returns>产品特性</returns>
        public List<ProductPropertyEntity> PropertyFilter(int classId) {
            List<ProductPropertyEntity> list = new List<ProductPropertyEntity>();

            SqlParameter[] parameters = 
            {
                new SqlParameter("@ClassId",SqlDbType.Int){ Value=classId }
            };

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_filter, parameters);

            while (reader.Read()) {
                list.Add(new ProductPropertyEntity() {
                    PropertyId = Convert.ToInt32(reader["PropertyId"]),
                    PropertyName = reader["Name"].ToString(),
                    PropertyValId = Convert.ToInt32(reader["PropertyValId"]),
                    PropertyValue = reader["PropertyValue"].ToString()
                });
            }
            reader.Close();

            return list;
        }
        /// <summary>
        /// 部件列表
        /// </summary>
        /// <returns></returns>
        public List<ProductPartEntity> GetPartList(bool? isValid) {
            List<ProductPartEntity> parts = new List<ProductPartEntity>();

            SqlParameter[] parameters = 
            {
                new SqlParameter("@IsValid",SqlDbType.Bit){ Value = isValid}
            };

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_partList, parameters);
            while (reader.Read()) {
                parts.Add(new ProductPartEntity() {
                    PartId = Convert.ToInt32(reader["PartId"]),
                    PartType = Convert.ToInt32(reader["Type"]),
                    Title = reader["PartName"].ToString(),
                    IsVaild = Convert.ToBoolean(reader["IsValid"]),
                    Sequence = Convert.ToInt32(reader["Sequence"])
                });
            }
            reader.Close();

            return parts;
        }
        /// <summary>
        /// 获取部件产品
        /// </summary>
        public ProductPartEntity GetProductPart(int partId, bool? isValid) {
            ProductPartEntity productPart = null;

            SqlParameter[] parameters =
            {
                new SqlParameter("@Partid",SqlDbType.Int){Value = partId},
                new SqlParameter("@IsValid",SqlDbType.Bit){ Value = isValid}
            };

            SqlDataReader reader = SqlDbProcedure.ExecuteReader(_partSelect, parameters);
            if (reader.HasRows) {
                productPart = new ProductPartEntity();
                productPart.ProductList = new List<ProductEntity>();

                reader.Read();

                productPart.Title = reader["PartName"].ToString();
                productPart.PartType = Convert.ToInt32(reader["PartType"]);
                productPart.Count = Convert.ToInt32(reader["PageSize"]);
                productPart.IsVaild = Convert.ToBoolean(reader["IsValid"]);
                productPart.ClassId = Convert.ToInt32(reader["ClassId"]);
                productPart.Sequence = Convert.ToInt32(reader["Sequence"]);
                productPart.PartId = partId;

                reader.NextResult();
                while (reader.Read()) {
                    productPart.ProductList.Add(new ProductEntity() {
                        SubName = reader["SubName"].ToString(),
                        ProductName = reader["ProductName"].ToString(),
                        ProductId = Convert.ToInt32(reader["ProductId"]),
                        ImageName = reader["ImageName"].ToString(),
                        Price = Convert.ToDecimal(reader["Price"]),
                        DisCount = Convert.ToDouble(reader["Discount"])
                    });
                }
            }

            return productPart;
        }
        /// <summary>
        /// 获取相关产品
        /// </summary>
        public ProductPartEntity RelateList(int ProductId) {
            ProductPartEntity part = new ProductPartEntity();
            part.ProductList = new List<ProductEntity>();
            SqlDataReader reader = SqlDbProcedure.ExecuteReader(
                _relateSelect
                , new SqlParameter[]
                    {
                        new SqlParameter("@ProductId",SqlDbType.Int){Value = ProductId}
                    }
                );
            if (reader.HasRows) {
                int i = 0;
                while (reader.Read()) {
                    // 计数，取第一行的Title
                    if (i < 1)
                        part.Title = reader["Title"].ToString();
                    i++;

                    part.ProductList.Add(new ProductEntity() {
                        ProductId = Convert.ToInt32(reader["ProductId"]),
                        SubName = reader["SubName"].ToString(),
                        ProductName = reader["ProductName"].ToString(),
                        Price = Convert.ToDecimal(reader["Price"])
                    });
                }
            }
            reader.Close();
            return part;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="moduleKey"></param>
        /// <returns></returns>
        public ProductPartEntity SameProduct(int productId, ProductPart moduleKey) {
            SqlParameter[] parameters = 
            {
                new SqlParameter("@ModuleKey",SqlDbType.NVarChar,50){Value= moduleKey},
                new SqlParameter("@ProductId",SqlDbType.Int){Value = productId}
            };
            object o = SqlDbProcedure.ExecuteScalar(_sameSelect, parameters);
            return GetProductPart(object.Equals(o, null) ? 0 : (int)o, null);
        }


    }
}
